﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer;
using System.Data;
using Parameter;
using System.Configuration;
using System.Web.Security;
using System.Security.Cryptography;
using AteckCsharp.Classes;
namespace AteckCsharp.Classes
{
    public class DAL
    {

        private static object accessDB(string storedProcedure, List<Param> myParam, bool scalar = false)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["fadi"].ConnectionString);
            conn.Open();

            // sqlcommand is the stored procedure command.
            // Add ds, command.
            SqlCommand cmd = new SqlCommand(storedProcedure, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();

            try
            {
                // adding parameter depending on the number of parameter in the param list object.
                for (int i = 0; i <= myParam.Count - 1; i++)
                {
                    if (myParam.ElementAt(i).paramType == SqlDbType.Int)
                    {
                        if (myParam.ElementAt(i).paramValue == null)
                        {
                            cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = DBNull.Value;
                        }
                        else
                        {
                            cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = int.Parse(myParam.ElementAt(i).paramValue);
                        }
                    }
                    else if (myParam.ElementAt(i).paramType == SqlDbType.DateTime)
                    {
                        if (myParam.ElementAt(i).paramValue == null)
                        {
                            cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = DBNull.Value;
                        }
                        else
                        {
                            cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = DateTime.Parse(myParam.ElementAt(i).paramValue);
                        }

                    }
                    else if (myParam.ElementAt(i).paramType == SqlDbType.Bit)
                    {
                        cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = bool.Parse(myParam.ElementAt(i).paramValue);

                    }
                    else if (myParam.ElementAt(i).paramType == SqlDbType.Float)
                    {
                        if (myParam.ElementAt(i).paramValue == null)
                        {
                            cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = DBNull.Value;
                        }
                        else
                        {
                            cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = decimal.Parse(myParam.ElementAt(i).paramValue);
                        }
                    }
                    else if (myParam.ElementAt(i).paramType == SqlDbType.Image)
                    {
                        if (myParam.ElementAt(i).paramValue == null)
                        {
                            cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = DBNull.Value;
                        }
                        else
                        {
                            cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = myParam.ElementAt(i).paramValue;
                        }
                    }
                    else if (myParam.ElementAt(i).paramType == SqlDbType.UniqueIdentifier)
                    {
                        cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = Guid.Parse(myParam.ElementAt(i).paramValue);//(System.Guid)myParam.ElementAt(i).paramValue;
                        //Guid.Parse(myParam.ElementAt(i).paramValue)
                    }
                    else
                    {
                        if (myParam.ElementAt(i).paramValue == null)
                        {
                            cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = DBNull.Value;
                        }
                        else
                        {
                            cmd.Parameters.Add(new SqlParameter(myParam.ElementAt(i).paramName, myParam.ElementAt(i).paramType)).Value = myParam.ElementAt(i).paramValue;
                        }

                    }

                }

                //Filling the dataSet
                if (scalar)
                {
                    int a = 0;
                    a = cmd.ExecuteNonQuery();
                    if (conn != null)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                    return a;
                }
                else
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds);
                    if (conn != null)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                    return ds;
                }

            }
            catch (Exception ex)
            {
                //Add to Error Log
            }
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
            return -2;
        }
        /// <summary>
        /// THIS function access database with no parameters
        /// it is used when you want to get all records from a table where there is no
        /// parameters passed to to the stored procedure
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <returns></returns>
        private static DataSet accessDD(string storedProcedure)
        {

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["fadi"].ConnectionString);
            try
            {
                conn.Open();

            }
            catch (Exception ex)
            {
                //Add to Error Log;
            }

            // sqlcommand is the stored procedure command.
            // Add ds, command.
            SqlCommand cmd = new SqlCommand(storedProcedure, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            try
            {
                //Filling the dataSet
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                //Add to error log.

            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return null;
        }
        #region DefaultPage
        public Boolean UpdateTimeSheet(Hours hour)
        {
            
            bool retVal = false;
            List<Param> myparam = new List<Param>();
            string storedPro = "UpdateTimeSheet";
                myparam.Add(new Param("@userId", hour.UserId, SqlDbType.UniqueIdentifier));
                myparam.Add(new Param("@projectId", hour.ProjectId, SqlDbType.Int));
                myparam.Add(new Param("@sun", hour.Sun, SqlDbType.Int));
                myparam.Add(new Param("@mon", hour.Mon, SqlDbType.Int));
                myparam.Add(new Param("@tue", hour.Tue, SqlDbType.Int));
                myparam.Add(new Param("@wed", hour.Wed, SqlDbType.Int));
                myparam.Add(new Param("@thur", hour.Thur, SqlDbType.Int));
                myparam.Add(new Param("@fri", hour.Fri, SqlDbType.Int));
                myparam.Add(new Param("@sat", hour.Sat, SqlDbType.Int));
                myparam.Add(new Param("@timeSheetId", hour.TimeSheetId, SqlDbType.Int));
                myparam.Add(new Param("@rowTotal", hour.RowTotal, SqlDbType.Int));
                int a = -1;
                try
                {
                    a = (int)accessDB(storedPro, myparam, true);
                    if (a == -2)
                        return false;
                    if (a != 0)
                        return true;
                    return false;
                }
                catch (Exception ex)
                {
                    //Add to ErrorLog.
                }

            return retVal;
        }
        public Boolean CreateTimeSheet(Hours hour)
        {
         
            bool retVal = false;
            List<Param> myparam = new List<Param>();
            string storedPro = "CreateHours";
                myparam.Add(new Param("@userId", hour.UserId, SqlDbType.UniqueIdentifier));
                myparam.Add(new Param("@projectId", hour.ProjectId, SqlDbType.Int));
                myparam.Add(new Param("@year", hour.Year, SqlDbType.VarChar));
                myparam.Add(new Param("@weekNo", hour.WeekNo, SqlDbType.Int));
                myparam.Add(new Param("@sun", hour.Sun, SqlDbType.Int));
                myparam.Add(new Param("@mon", hour.Mon, SqlDbType.Int));
                myparam.Add(new Param("@tue", hour.Tue, SqlDbType.Int));
                myparam.Add(new Param("@wed", hour.Wed, SqlDbType.Int));
                myparam.Add(new Param("@thur", hour.Thur, SqlDbType.Int));
                myparam.Add(new Param("@fri", hour.Fri, SqlDbType.Int));
                myparam.Add(new Param("@sat", hour.Sat, SqlDbType.Int));
                myparam.Add(new Param("@isPayRoll", hour.IsPayRoll, SqlDbType.Bit));
                myparam.Add(new Param("@weekStartDate", hour.WeekStartDate, SqlDbType.Date));
                myparam.Add(new Param("@rowTotal", hour.RowTotal, SqlDbType.Int));
                int a = -1;
                try
                {
                    a = (int)accessDB(storedPro, myparam, true);
                    if (a == -2)
                        return false;
                    if (a != 0)
                       return true;
                    return false;
                }
                catch (Exception ex)
                {
                    //Add to ErrorLog.
                }
            return retVal;
        }
        public DataSet GetExistingWeek(String userId, String weekNo)
        {
            DataSet ds = new DataSet();
           
            List<Param> myparam = new List<Param>();
            string storedPro = "GetExistingWeek";
                myparam.Add(new Param("@userId", userId, SqlDbType.UniqueIdentifier));
                myparam.Add(new Param("@weekNo", weekNo, SqlDbType.Int));
                try
                {
                   ds = (DataSet) accessDB(storedPro, myparam, false);
                    return ds;
                }
                catch (Exception ex)
                {
                    //Add to ErrorLog.
                }
            return null;
        }
        #endregion
        #region PersonalInfo
        public DataSet GetPersonalInfo(String userId)
        {
            DataSet ds = new DataSet();

            List<Param> myparam = new List<Param>();
            string storedPro = "GetPersonalInfo";
            myparam.Add(new Param("@userId", userId, SqlDbType.UniqueIdentifier));
            try
            {
                ds = (DataSet)accessDB(storedPro, myparam, false);
                return ds;
            }
            catch (Exception ex)
            {
                //Add to ErrorLog.
            }
            return null;
        }
        public Boolean UpdatePersonalInfo(PresonalInfo info)
        {

            bool retVal = false;
            List<Param> myparam = new List<Param>();
            string storedPro = "UpdatePersonalInfo";
            myparam.Add(new Param("@userId", info.userId, SqlDbType.UniqueIdentifier));
            myparam.Add(new Param("@street", info.Street, SqlDbType.VarChar));
            myparam.Add(new Param("@state", info.State, SqlDbType.VarChar));
            myparam.Add(new Param("@city", info.City, SqlDbType.VarChar));
            myparam.Add(new Param("@zip", info.Zip, SqlDbType.VarChar));
            myparam.Add(new Param("@firstName", info.FirstName, SqlDbType.VarChar));
            myparam.Add(new Param("@lastName", info.LastName, SqlDbType.VarChar));
            myparam.Add(new Param("@dob", info.DOB, SqlDbType.VarChar));
            myparam.Add(new Param("@phoneNo", info.PhoneNo, SqlDbType.VarChar));
            
            int a = -1;
            try
            {
                a = (int)accessDB(storedPro, myparam, true);
                if (a == -2)
                    return false;
                if (a != 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                //Add to ErrorLog.
            }

            return retVal;
        }
        #endregion
    }

}

